import datetime

import matplotlib.pyplot as plt
import pandas as pd
import pymysql
import matplotlib.ticker as mticker
import matplotlib.dates as mdate

# 建立数据库连接
from pandas.plotting._matplotlib import timeseries

conn = pymysql.connect("localhost", "stockx", "stockx#2020", "stockx", charset="utf8")
print("连接成功")
# 读取数据库表数据
data = pd.read_sql("select dt , pb_ewpvo as pb,   pb_ewpvo_val_pos as pos, cp from index_value_info "
                   "where index_code = '000300' ", con=conn)
# 数据转化为列表
x = list(data.dt)  # 日期
y1 = list(data.pb)
y2 = list(data.cp)
y3 = list(data.pos)

# 下面2个只针对windows系统
plt.rcParams['font.sans-serif'] = ['SimHei']  # 解决中文乱码
plt.rcParams['axes.unicode_minus'] = False  # 解决保存图像时符号-显示为方块的2问题


fig, ax1 = plt.subplots(figsize=(14, 4))  # a figure with a single Axes
ax1.set_title('等权PB & 指数值')
ax1.set_xlim(x[0], x[-1])

ax1.set_xlabel("")
color = 'tab:blue'
ax1.set_ylabel("PB", color=color)
ax1.tick_params(axis='y', labelcolor=color)
ax1.xaxis.set_tick_params(rotation=45, color=color)
ax1.xaxis.set_major_formatter(mdate.DateFormatter('%Y-%m')) #去掉day
xtcik = pd.date_range(x[0], x[-1], freq='90d')
ax1.set_xticks(xtcik)
ax1.plot(x, y1)


ax2 = ax1.twinx()  # instantiate a second axes that shares the same x-axis
color = 'tab:red'
ax2.set_ylabel('Index Value', color=color)
ax2.plot(x, y2, color=color)
ax2.tick_params(axis='y', labelcolor=color)
ax2.xaxis.set_major_formatter(mdate.DateFormatter('%Y-%m')) #去掉day
ax2.set_xticks(xtcik)

ax3 = ax1.twinx()  # instantiate a second axes that shares the same x-axis
color = 'tab:green'
ax3.set_ylabel('% Position', color=color)
ax3.tick_params(axis='y', labelcolor=color)
ax3.xaxis.set_major_formatter(mdate.DateFormatter('%Y-%m')) #去掉day
#ax3.yaxis.set_label_coords(0.5, 0.5)
lspine = ax3.spines['right']
lspine.set_position(('axes', 1.05))
ax3.set_xticks(xtcik)
ax3.plot(x, y3, color=color)


fig.tight_layout()  # otherwise the right y-label is slightly clipped
plt.grid()
plt.legend()
plt.show()

print(type(x))
# 关闭数据库连接
conn.close()


'''
    '1000002': '沪深A股',
    '1000003': '深圳A股',
    '1000004': '上海A股',
    '1000007': '创业板全指',
    '1000011': '沪深非金融A股',
    '1000012': '中小板全指',
    '399006': '创业板指数',
    '000016': '上证50指数',
    '000903': '中证100',
    '399330': '深证100',
    '000300': '沪深300',
    '000905': '中证500',
    '000913': '沪深300医药',
    '399971': '中证传媒',
    '399986': '中证银行',
    '399975': '全指证券',
    '000932': '中证主要消费',
    '000931': '中证可选消费',
    '000991': '全指医药',
    '000993': '全指信息技术',
    '399967': '中证军工',
    '000827': '中证环保',
    '399812': '中证养老',
    '000015': '上证红利',
    '000922': '中证红利',
    'H30269': '中证红利低波',
    'H30533': '海外中概50指数',
'''
